<?php

/**
 * stats actions.
 *
 * @package    sf_sandbox
 * @subpackage stats
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class statsActions extends sfActions
{

  public function preExecute()
  {
    //default mode is weekly
    //default date range is current month
    
    if($this->hasRequestParameter('rangeA'))
    {
      $input = $this->getRequestParameter('rangeA');
      
      $findme   = ' - ';
      $pos = strpos($input, $findme);
      if ($pos === false) {
        $this->start_date = $input;
        $this->end_date = $input;
      } else {
        $dates = explode(' - ', $input); 
        $this->start_date = $dates[0];
        $this->end_date = $dates[1];
      }
     
    }else{
      //current month
      $this->start_date = date( 'Y-m-d', mktime(0, 0, 0, date("m")  , 1, date("Y")) );
      $this->end_date = date( 'Y-m-d', mktime(0, 0, 0, date("m") + 1  , 0, date("Y")) );
    }
    
    if(empty($input)){
      //current month
      $this->start_date = date( 'Y-m-d', mktime(0, 0, 0, date("m")  , 1, date("Y")) );
      $this->end_date = date( 'Y-m-d', mktime(0, 0, 0, date("m") + 1  , 0, date("Y")) );
    }
    

      $this->mode = $this->getRequestParameter('mode');

//echo $this->mode;    

  }
 /**
  * Executes index action
  *
  * @param sfRequest $request A request object
  */
  public function executeIndex(sfWebRequest $request)
  {
    //$this->forward('default', 'module');
  }
  
  public function executeUserLoginHistory(sfWebRequest $request)
  {
  
    /*
    ** this is the first data set in array
    ** this data set will have all the xAxis 
    */
    $q = Doctrine_Query::create()
  ->select("DATE_FORMAT(created_at, '%Y') AS y , WEEKOFYEAR(`created_at`)  AS d, COUNT(*) as number , DATE_FORMAT(subdate(created_at, INTERVAL weekday(created_at) DAY), '%Y-%m-%d') AS start_at, DATE_FORMAT(adddate(created_at, INTERVAL 6-weekday(created_at) DAY) , '%Y-%m-%d') AS end_at")
  ->from('userLoginHistory')
  ->where('created_at >= "'.$this->start_date.'"  AND created_at <= "'.$this->end_date.'"')  
  ->groupBy('d')
  ->orderBy('y,d');

   $r = $q->fetchArray();
   $data_set = array();
   $data_set[] = array('name' => 'How many user logged in within the week', 'data' => $r);
  

   $r = $this->getWeeklyLoginCount(' = ', 1);
   $data_set[] = array('name' => 'User login 1 time', 'data' => $r);   

   $r = $this->getWeeklyLoginCount(' = ', 2);
   $data_set[] = array('name' => 'User login 2 time', 'data' => $r); 
   
   $r = $this->getWeeklyLoginCount(' = ', 3);
   $data_set[] = array('name' => 'User login 3 time', 'data' => $r); 
   
    $r = $this->getWeeklyLoginCount(' = ', 4);
   $data_set[] = array('name' => 'User login 3 time', 'data' => $r);   
   
   $r = $this->getWeeklyLoginCount(' = ', 4);
   $data_set[] = array('name' => 'User login 4 time', 'data' => $r); 
   
   $r = $this->getWeeklyLoginCount(' > ', 5);
   $data_set[] = array('name' => 'User login more than 5 time', 'data' => $r);          
   
   $this->stats = array(
    'title' => 'User Login History',
    'yAixsTitle' => 'Weekly Number',
    'cmd' => 'user_login_history',
    'data' => $data_set
   );
   
  }
  
  
  private function getWeeklyLoginCount($operator = ' = ', $value = '1')
  {
 
    $query = "
    
    select IFNULL(tbl2.number, 0) AS number, tbl3.d, tbl3.y, tbl3.start_at, tbl3.end_at from
    (
    
      select count(tbl.user_id) as number, tbl.d, tbl.y, tbl.start_at, tbl.end_at from
      (
      select user_id, count(user_id) as number, DATE_FORMAT(created_at, '%Y') AS y ,  WEEKOFYEAR(`created_at`) as d, DATE_FORMAT(subdate(created_at, INTERVAL weekday(created_at) DAY), '%Y-%m-%d') AS start_at, DATE_FORMAT(adddate(created_at, INTERVAL 6-weekday(created_at) DAY) , '%Y-%m-%d') AS end_at, created_at
      from user_login_history 
      group by d, user_id
      having number {$operator} {$value} AND `created_at` >= '{$this->start_date}' AND `created_at` <= '{$this->end_date}'
      ) as tbl
      
      group by tbl.y, tbl.d
    
    ) as tbl2
    
    
    right join
    (
    select DATE_FORMAT(created_at, '%Y') AS y ,  WEEKOFYEAR(`created_at`) as d, DATE_FORMAT(subdate(created_at, INTERVAL weekday(created_at) DAY), '%Y-%m-%d') AS start_at, DATE_FORMAT(adddate(created_at, INTERVAL 6-weekday(created_at) DAY) , '%Y-%m-%d') AS end_at
    from user_login_history 
    group by d
    ) as tbl3
    
    on (tbl3.y = tbl2.y and tbl3.d=tbl2.d)
    
    order by tbl3.y, tbl3.d
    
    ";
    $r = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query); 
    return $r; 
  } 


  public function executeNewSignUp(sfWebRequest $request)
  {
  
    /*
    ** this is the first data set in array
    ** this data set will have all the xAxis 
    */
    $q = Doctrine_Query::create()
  ->select("DATE_FORMAT(created, '%Y') AS y , WEEKOFYEAR(`created`)  AS d, COUNT(*) as number , DATE_FORMAT(subdate(created, INTERVAL weekday(created) DAY), '%Y-%m-%d') AS start_at, DATE_FORMAT(adddate(created, INTERVAL 6-weekday(created) DAY) , '%Y-%m-%d') AS end_at")
  ->from('member')
  ->where('created >= "'.$this->start_date.'"  AND created <= "'.$this->end_date.'"')  
  ->groupBy('d')
  ->orderBy('y,d');

   $r = $q->fetchArray();
   $data_set = array();
   $data_set[] = array('name' => 'weekly new signup', 'data' => $r);
       
   
   $this->stats = array(
    'title' => 'Weekly New Signup',
    'yAixsTitle' => 'Weekly Number',
    'cmd' => 'member',
    'data' => $data_set
   );
   
  }  
}
